﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using DingTalkAgent.DBUtility;
namespace DingTalkAgent.SqlData
{
    //员工信息
    public partial class users
    {
        #region	 T_users

        ///<summary>
        /// Exist
        ///</summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_users");
            strSql.Append(" where ");
            strSql.Append(" id = @id  ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条T_users数据
        /// </summary>
        public int Add(DingTalkAgent.Model.users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_users(");
            strSql.Append("remark,email,orgEmail,jobnumber,isHide,isSenior,extattr,realDepartment,lxMobile,userid,name,department,position,roleid,mobile,tel,workPlace");
            strSql.Append(") values (");
            strSql.Append("@remark,@email,@orgEmail,@jobnumber,@isHide,@isSenior,@extattr,@realDepartment,@lxMobile,@userid,@name,@department,@position,@roleid,@mobile,@tel,@workPlace");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                        new SqlParameter("@remark", SqlDbType.VarChar,2048) ,
                        new SqlParameter("@email", SqlDbType.VarChar,256) ,
                        new SqlParameter("@orgEmail", SqlDbType.VarChar,256) ,
                        new SqlParameter("@jobnumber", SqlDbType.VarChar,256) ,
                        new SqlParameter("@isHide", SqlDbType.Bit,1) ,
                        new SqlParameter("@isSenior", SqlDbType.Bit,1) ,
                        new SqlParameter("@extattr", SqlDbType.VarChar,4000) ,
                        new SqlParameter("@realDepartment", SqlDbType.VarChar,256) ,
                        new SqlParameter("@lxMobile", SqlDbType.VarChar,256) ,
                        new SqlParameter("@userid", SqlDbType.NVarChar,64) ,
                        new SqlParameter("@name", SqlDbType.VarChar,128) ,
                        new SqlParameter("@department", SqlDbType.VarChar,256) ,
                        new SqlParameter("@position", SqlDbType.VarChar,256) ,
                        new SqlParameter("@roleid", SqlDbType.Int,4) ,
                        new SqlParameter("@mobile", SqlDbType.VarChar,64) ,
                        new SqlParameter("@tel", SqlDbType.VarChar,64) ,
                        new SqlParameter("@workPlace", SqlDbType.VarChar,256)

            };

            parameters[0].Value = model.remark;
            parameters[1].Value = model.email;
            parameters[2].Value = model.orgEmail;
            parameters[3].Value = model.jobnumber;
            parameters[4].Value = model.isHide;
            parameters[5].Value = model.isSenior;
            parameters[6].Value = model.extattr;
            parameters[7].Value = model.realDepartment;
            parameters[8].Value = model.lxMobile;
            parameters[9].Value = model.userid;
            parameters[10].Value = model.name;
            parameters[11].Value = model.department;
            parameters[12].Value = model.position;
            parameters[13].Value = model.roleid;
            parameters[14].Value = model.mobile;
            parameters[15].Value = model.tel;
            parameters[16].Value = model.workPlace;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条T_users数据
        /// </summary>
        public bool Update(DingTalkAgent.Model.users model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_users set ");

            strSql.Append(" remark = @remark , ");
            strSql.Append(" email = @email , ");
            strSql.Append(" orgEmail = @orgEmail , ");
            strSql.Append(" jobnumber = @jobnumber , ");
            strSql.Append(" isHide = @isHide , ");
            strSql.Append(" isSenior = @isSenior , ");
            strSql.Append(" extattr = @extattr , ");
            strSql.Append(" realDepartment = @realDepartment , ");
            strSql.Append(" lxMobile = @lxMobile , ");
            strSql.Append(" userid = @userid , ");
            strSql.Append(" name = @name , ");
            strSql.Append(" department = @department , ");
            strSql.Append(" position = @position , ");
            strSql.Append(" roleid = @roleid , ");
            strSql.Append(" mobile = @mobile , ");
            strSql.Append(" tel = @tel , ");
            strSql.Append(" workPlace = @workPlace  ");
            strSql.Append(" where id=@id ");

            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,4) ,
                        new SqlParameter("@remark", SqlDbType.VarChar,2048) ,
                        new SqlParameter("@email", SqlDbType.VarChar,256) ,
                        new SqlParameter("@orgEmail", SqlDbType.VarChar,256) ,
                        new SqlParameter("@jobnumber", SqlDbType.VarChar,256) ,
                        new SqlParameter("@isHide", SqlDbType.Bit,1) ,
                        new SqlParameter("@isSenior", SqlDbType.Bit,1) ,
                        new SqlParameter("@extattr", SqlDbType.VarChar,4000) ,
                        new SqlParameter("@realDepartment", SqlDbType.VarChar,256) ,
                        new SqlParameter("@lxMobile", SqlDbType.VarChar,256) ,
                        new SqlParameter("@userid", SqlDbType.NVarChar,64) ,
                        new SqlParameter("@name", SqlDbType.VarChar,128) ,
                        new SqlParameter("@department", SqlDbType.VarChar,256) ,
                        new SqlParameter("@position", SqlDbType.VarChar,256) ,
                        new SqlParameter("@roleid", SqlDbType.Int,4) ,
                        new SqlParameter("@mobile", SqlDbType.VarChar,64) ,
                        new SqlParameter("@tel", SqlDbType.VarChar,64) ,
                        new SqlParameter("@workPlace", SqlDbType.VarChar,256)

            };

            parameters[0].Value = model.id;
            parameters[1].Value = model.remark;
            parameters[2].Value = model.email;
            parameters[3].Value = model.orgEmail;
            parameters[4].Value = model.jobnumber;
            parameters[5].Value = model.isHide;
            parameters[6].Value = model.isSenior;
            parameters[7].Value = model.extattr;
            parameters[8].Value = model.realDepartment;
            parameters[9].Value = model.lxMobile;
            parameters[10].Value = model.userid;
            parameters[11].Value = model.name;
            parameters[12].Value = model.department;
            parameters[13].Value = model.position;
            parameters[14].Value = model.roleid;
            parameters[15].Value = model.mobile;
            parameters[16].Value = model.tel;
            parameters[17].Value = model.workPlace;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条T_users数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_users ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条T_users数据
        /// </summary>
        public bool Deletes(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_users ");
            strSql.Append(" where ID in (" + ids + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DingTalkAgent.Model.users GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id, remark, email, orgEmail, jobnumber, isHide, isSenior, extattr, realDepartment, lxMobile, userid, name, department, position, roleid, mobile, tel, workPlace  ");
            strSql.Append("  from T_users ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;


            DingTalkAgent.Model.users model = new DingTalkAgent.Model.users();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
                model.email = ds.Tables[0].Rows[0]["email"].ToString();
                model.orgEmail = ds.Tables[0].Rows[0]["orgEmail"].ToString();
                model.jobnumber = ds.Tables[0].Rows[0]["jobnumber"].ToString();
                if (ds.Tables[0].Rows[0]["isHide"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isHide"].ToString() == "1") || (ds.Tables[0].Rows[0]["isHide"].ToString().ToLower() == "true"))
                    {
                        model.isHide = true;
                    }
                    else
                    {
                        model.isHide = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isSenior"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isSenior"].ToString() == "1") || (ds.Tables[0].Rows[0]["isSenior"].ToString().ToLower() == "true"))
                    {
                        model.isSenior = true;
                    }
                    else
                    {
                        model.isSenior = false;
                    }
                }
                model.extattr = ds.Tables[0].Rows[0]["extattr"].ToString();
                model.realDepartment = ds.Tables[0].Rows[0]["realDepartment"].ToString();
                model.lxMobile = ds.Tables[0].Rows[0]["lxMobile"].ToString();
                model.userid = ds.Tables[0].Rows[0]["userid"].ToString();
                model.name = ds.Tables[0].Rows[0]["name"].ToString();
                model.department = ds.Tables[0].Rows[0]["department"].ToString();
                model.position = ds.Tables[0].Rows[0]["position"].ToString();
                if (ds.Tables[0].Rows[0]["roleid"].ToString() != "")
                {
                    model.roleid = int.Parse(ds.Tables[0].Rows[0]["roleid"].ToString());
                }
                model.mobile = ds.Tables[0].Rows[0]["mobile"].ToString();
                model.tel = ds.Tables[0].Rows[0]["tel"].ToString();
                model.workPlace = ds.Tables[0].Rows[0]["workPlace"].ToString();

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据T_users列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM T_users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据T_users列表
        /// </summary>
        public IEnumerable<DingTalkAgent.Model.users> IEnumGetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id, remark, email, orgEmail, jobnumber, isHide, isSenior, extattr, realDepartment, lxMobile, userid, name, department, position, roleid, mobile, tel, workPlace ");
            strSql.Append(" FROM T_users ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            DingTalkAgent.Model.users model;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                model = new DingTalkAgent.Model.users();
                if (dr["id"].ToString() != "")
                {
                    model.id = int.Parse(dr["id"].ToString());
                }
                model.remark = dr["remark"].ToString();
                model.email = dr["email"].ToString();
                model.orgEmail = dr["orgEmail"].ToString();
                model.jobnumber = dr["jobnumber"].ToString();
                if (dr["isHide"].ToString() != "")
                {
                    if ((dr["isHide"].ToString() == "1") || (dr["isHide"].ToString().ToLower() == "true"))
                    {
                        model.isHide = true;
                    }
                    else
                    {
                        model.isHide = false;
                    }
                }
                if (dr["isSenior"].ToString() != "")
                {
                    if ((dr["isSenior"].ToString() == "1") || (dr["isSenior"].ToString().ToLower() == "true"))
                    {
                        model.isSenior = true;
                    }
                    else
                    {
                        model.isSenior = false;
                    }
                }
                model.extattr = dr["extattr"].ToString();
                model.realDepartment = dr["realDepartment"].ToString();
                model.lxMobile = dr["lxMobile"].ToString();
                model.userid = dr["userid"].ToString();
                model.name = dr["name"].ToString();
                model.department = dr["department"].ToString();
                model.position = dr["position"].ToString();
                if (dr["roleid"].ToString() != "")
                {
                    model.roleid = int.Parse(dr["roleid"].ToString());
                }
                model.mobile = dr["mobile"].ToString();
                model.tel = dr["tel"].ToString();
                model.workPlace = dr["workPlace"].ToString();

                yield return model;
            }
        }
        /// <summary>
        /// 获得数据 T_users分页列表
        /// </summary>
        /// <param name="PageIndex">页码</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="total">总记录数</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public List<DingTalkAgent.Model.users> GetPageList(int PageIndex, int PageSize, out int total, string keyword)
        {
            int Skip = (PageIndex - 1) * PageSize;
            //count
            StringBuilder strSqlCount = new StringBuilder();
            strSqlCount.Append("Select @total=count(id) From T_users ");
            //data
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Top " + PageSize + " * from (");
            strSql.Append("select  row_number() OVER (ORDER BY id Desc) AS row, id, remark, email, orgEmail, jobnumber, isHide, isSenior, extattr, realDepartment, lxMobile, userid, name, department, position, roleid, mobile, tel, workPlace ");
            strSql.Append(" FROM  T_users ");
            //where
            StringBuilder Where = new StringBuilder();
            Where.Append(" where 1=1");
            if (keyword.Trim() != "")
            {
                Where.Append(" and (Title like @keyword or content like @keyword) ");
            }


            strSqlCount.Append(Where.ToString());
            strSql.Insert(0, strSqlCount.ToString() + ";");
            strSql.Append(Where.ToString());
            strSql.Append(") as a ");
            strSql.Append(" Where a.row>" + Skip);

            //parameters
            SqlParameter[] parameters = {
                    new SqlParameter("@total", SqlDbType.Int,4),
                    new SqlParameter("@keyword", SqlDbType.VarChar,64)
                    };
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = "%" + keyword.Trim() + "%";

            List<DingTalkAgent.Model.users> list = new List<DingTalkAgent.Model.users>();
            DingTalkAgent.Model.users model = new DingTalkAgent.Model.users();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            total = Convert.ToInt32(parameters[0].Value);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                model = new DingTalkAgent.Model.users();
                if (dr["id"].ToString() != "")
                {
                    model.id = int.Parse(dr["id"].ToString());
                }
                model.remark = dr["remark"].ToString();
                model.email = dr["email"].ToString();
                model.orgEmail = dr["orgEmail"].ToString();
                model.jobnumber = dr["jobnumber"].ToString();
                if (dr["isHide"].ToString() != "")
                {
                    if ((dr["isHide"].ToString() == "1") || (dr["isHide"].ToString().ToLower() == "true"))
                    {
                        model.isHide = true;
                    }
                    else
                    {
                        model.isHide = false;
                    }
                }
                if (dr["isSenior"].ToString() != "")
                {
                    if ((dr["isSenior"].ToString() == "1") || (dr["isSenior"].ToString().ToLower() == "true"))
                    {
                        model.isSenior = true;
                    }
                    else
                    {
                        model.isSenior = false;
                    }
                }
                model.extattr = dr["extattr"].ToString();
                model.realDepartment = dr["realDepartment"].ToString();
                model.lxMobile = dr["lxMobile"].ToString();
                model.userid = dr["userid"].ToString();
                model.name = dr["name"].ToString();
                model.department = dr["department"].ToString();
                model.position = dr["position"].ToString();
                if (dr["roleid"].ToString() != "")
                {
                    model.roleid = int.Parse(dr["roleid"].ToString());
                }
                model.mobile = dr["mobile"].ToString();
                model.tel = dr["tel"].ToString();
                model.workPlace = dr["workPlace"].ToString();

                list.Add(model);
            }
            return list;
        }
        #endregion


        #region 李向荣
        /// <summary>
        /// 移动端免登录
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DingTalkAgent.Model._view_account login(string userid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT
	                       b.id as depId
                          ,b.name as depName
	                      ,b.leveCode as leveCode
	                      ,b.isOrg as isOrg
                          ,[userid]
                          ,a.[name]
                          ,[position]
                          ,[roleid]
                      FROM [T_users] as a
                      left join T_Department as b on a.department=b.id 
                        where userid=@userid
                    ");
            SqlParameter[] parameters = {
                    new SqlParameter("@userid", SqlDbType.VarChar,128)
            };
            parameters[0].Value = userid;


            DingTalkAgent.Model._view_account model = new DingTalkAgent.Model._view_account();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["depId"].ToString() != "")
                {
                    model.depId = int.Parse(ds.Tables[0].Rows[0]["depId"].ToString());
                }
                model.depName = ds.Tables[0].Rows[0]["depName"].ToString();
                model.leveCode = ds.Tables[0].Rows[0]["leveCode"].ToString();
                model.isOrg = int.Parse(ds.Tables[0].Rows[0]["isOrg"].ToString());
                model.userid = ds.Tables[0].Rows[0]["userid"].ToString();
                model.name = ds.Tables[0].Rows[0]["name"].ToString();
                model.position = ds.Tables[0].Rows[0]["position"].ToString();
                if (ds.Tables[0].Rows[0]["roleid"].ToString() != "")
                {
                    model.roleid = int.Parse(ds.Tables[0].Rows[0]["roleid"].ToString());
                }
              
                return model;
            }
            else
            {
                return null;
            }
        }
        #endregion
    }
}
